#include "opedb.h"
#include <QDebug>
#include <QSqlError>
#include <QMessageBox>
#include <QStringList>

OpeDB::OpeDB(QObject *parent)
    : QObject{parent}
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");

    this->init_db();
}

OpeDB::~OpeDB()
{
    m_db.close();
}

OpeDB &OpeDB::getInstence()
{
    static OpeDB instance;
    return instance;
}

void OpeDB::init_db()
{
    m_db.setHostName("localhost");
    // m_db.setDatabaseName("/home/b/Qt/networkProject/network_disk/TcpServer/cloud.db");
    // m_db.setDatabaseName("E:\\Desktop\\QTCode11\\project\\network_disk\\TcpServer\\cloud.db");
    m_db.setDatabaseName("E:\\Desktop\\QTCode11\\project\\network_disk\\TcpServerHash\\cloud.db");

    if (!m_db.open()) {
        qDebug() << "error falied to open" << m_db.lastError();
        QMessageBox::critical(NULL, "open database", "open database faild");
    }

#if 0
    QSqlQuery query;

    QString sqlSelect = QString("select * from userInfo");
    if (!query.exec(sqlSelect)) {
        qDebug() << "select data error" << m_db.lastError();
    } else {
        /*
sqlite> PRAGMA table_info(userInfo);
0|id|INTEGER|0||1
1|name|varchar(32)|0||0
2|pwd|varchar(32)|0||0
3|online|INTEGER|0|0|0
*/
        while (query.next()) {
            qDebug() << "id: \t" << query.value("id").toInt();
            qDebug() << "name: \t" << query.value("name").toString();
            qDebug() << "pwd: \t" << query.value("pwd").toString();
            qDebug() << "online: \t" << query.value("online").toInt();
            qDebug() << "-------------------------";
        }
    }
#endif

}

bool OpeDB::handle_regist(const char *name, const char *pwd)
{
    if (!name || !pwd) return false;
    QString data = QString("insert into userInfo(name, pwd) "
                           "values(\'%1\', \'%2\')").arg(name).arg(pwd);
    // qDebug() << data;
    QSqlQuery query;
    return query.exec(data);
}

bool OpeDB::handle_login(const char *name, const char *pwd)
{
    if (!name || !pwd) return false;
    QString sqlSelect = QString("select * from userInfo "
                "where name = \'%1\' and pwd = \'%2\' and online = 0;").arg(name).arg(pwd);
    // qDebug() << "查询语句:" << sqlSelect;

    QSqlQuery query;
    query.exec(sqlSelect);
    if (query.next() ) { //把online置为1
        // update userInfo set online = %1 where name = \'%2\';
        QString update = QString("update userInfo set "
                  "online = 1 where name = \'%1\';").arg(name);
        // qDebug() << update;
        query.exec(update);
        return true;
    }
    return false;
}

bool OpeDB::handle_logout(const char *name)
{
    if (!name) return false;

    QSqlQuery query;
    QString update = QString("update userInfo set "
                             "online = 0 where name = \'%1\';").arg(name);
    // qDebug() << update;
    query.exec(update);
    return true;
}

QStringList OpeDB::handle_show_online()
{
    QSqlQuery query;

    QStringList ret;
    QString sqlSelect = QString("select name from userInfo where online = 1");
    if (!query.exec(sqlSelect)) {
        qDebug() << "select data error" << m_db.lastError();
    } else {   
        while (query.next()) {
            ret.push_back(query.value("name").toString());
        }
    }
    // qDebug() << ret;
    return ret;
}

/*
handle_search_user返回值:
在线: 1
不在线: 0
不存在: -1
*/
int OpeDB::handle_search_user(const char *name)
{
    QSqlQuery query;

    QString sqlSelect = QString("select online from userInfo where name = \'%1\';").arg(name);
    if (!query.exec(sqlSelect)) {
        qDebug() << "select data error" << m_db.lastError();
    } else {
        if (query.next()) return query.value("online").toInt();
    }
    return -1;  //不存在;
}

int OpeDB::handle_get_userID(const char *name)
{
    QSqlQuery query;

    QString sqlSelect = QString("select id from userInfo where name = \'%1\';").arg(name);
    if (!query.exec(sqlSelect)) {
        qDebug() << "select data error" << m_db.lastError();
    } else {
        if (query.next()) return query.value("id").toInt();
    }
    return -1;  //不存在;
}
/*
handle_add_friend返回值:
参数的名字为空: -1;
已经是好友: 0;
对方在线: 1;
对方不在线: 2;
对方不存在: 3
*/
int OpeDB::handle_add_friend(const char *my_name, const char *user_name)
{
    if (!my_name || !user_name) return -1;
    //先查一下他们是不是已经是4好友了
    QString sqlSelect = QString("select f.id from friend f "
    "join  (select id from userInfo where name = \'%1\') u1, "
    "(select id from userInfo where name = \'%2\') u2 "
    "on f.id = u1.id and f.friendId = u2.id or "
                                "f.id = u2.id and f.friendId = u1.id;").arg(my_name).arg(user_name);
    // qDebug() << sqlSelect;

    QSqlQuery query;
    query.exec(sqlSelect);
    if (query.next() ) { //如果sql查询语句不为空, 说明对方已是好友
        return 0;
    }

    //不是好友:
    int status = this->handle_search_user(user_name);
    if (status == 1) { //在线
        // QString data = QString("insert into friend "
        //                        "values(\'%1\', \'%2\')").arg(my_name).arg(user_name);
        // query.exec(data);
        return 1;
    } else if (status == 0) { //不在线, 添加失败
        return 2;
    }

    return 3; //用户不存在
}

void OpeDB::handle_add_friend_agree(const char *my_name, const char *user_name)
{
    if (!my_name || !user_name) return;

    //不是好友:
    int status = this->handle_search_user(user_name);
    if (status == 1) { //在线
        int myID = handle_get_userID(my_name);
        int userID = handle_get_userID(user_name);
        if (myID == -1 || userID == -1) return; //不存在,return
        QString data = QString("insert into friend "
                               "values(\'%1\', \'%2\')").arg(myID).arg(userID);
        // qDebug() << data;
        QSqlQuery query;
        query.exec(data);
    }
}

int OpeDB::handle_del_friend(const char *my_name, const char *user_name)
{
    if (!my_name || !user_name) return -1;

    //不用查询他们是不是好友, 因为如果数据库中没有要删除的数据, 也不会报错
    QString del = QString(" delete from friend where "
                          "id = (select id from userInfo where name = '\%1\') "
                          "and "
                          "friendId = "
                          "(select id from userInfo where name = '\%2\');").arg(my_name).arg(user_name);
    QSqlQuery query;
    query.exec(del);

    //删除相反顺序的
    del = QString(" delete from friend where "
                          "id = (select id from userInfo where name = '\%1\') "
                          "and "
                          "friendId = "
                          "(select id from userInfo where name = '\%2\');").arg(user_name).arg(my_name);
    query.exec(del);

    return 1;
}

QStringList OpeDB::handle_flush_friend(const char* name)
{
    QStringList ret;
    if (name == NULL) return ret;
/*
select u.name from userInfo u join ( select f.id from friend f join (select id from userInfo where name = 'jack') s on f.friendId = s.id ) s2 on u.id = s2.id and online = 1;
*/
    //好友为name的
    QString sqlSelect = QString("select u.name from userInfo u "
                                    "join ( "
                                "select f.id from friend f "
                                    "join (select id from userInfo where name = \'%1\') s "
                                    "on f.friendId = s.id "
                                ") s2 "
                                "on u.id = s2.id and online = 1;").arg(name);
    // qDebug() << sqlSelect;

    QSqlQuery query;
    query.exec(sqlSelect);
    while (query.next()) {
        ret.push_back(query.value(0).toString());
    }

    //name的好友
    sqlSelect = QString("select u.name from userInfo u "
                        "join ( "
                        "select f.friendId from friend f "
                        "join (select id from userInfo where name = \'%1\') s "
                        "on f.id = s.id "
                        ") s2 "
                        "on u.id = s2.friendId and online = 1;").arg(name);
    // qDebug() << sqlSelect;
    query.exec(sqlSelect);
    while (query.next()) {
        ret.push_back(query.value(0).toString());
    }

    // qDebug() << ret;
    return ret;
}


